﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data.Linq;

namespace MyVideoViewerLibrary.MyMovies
{
    
    public class MyMoviesBusiness : MyVideoViewerLibrary.IMoviesBusiness
    {
        string[] extensions = new string[] { ".mov", ".avi", ".mpeg", ".mpg", ".mkv", ".wmv", ".mp4", ".dvr-ms", ".m2ts", ".ts", ".divx" };
        List<string> parentalRatings = new List<string>(new string[] { "", "Not Rated", "G", "PG", "PG-13", "NC-17", "R" });

        public static MyMoviesDataContext MyMoviesContext { get; set; }
        static MyMoviesBusiness()
        {
            ResetContext();
        }

        public static bool IsValid
        {
            get
            {
                SqlConnection conn = new SqlConnection(Settings.Default.MyMoviesConnectionString);
                SqlCommand cmd = new SqlCommand("select count(*) from sys.databases where name = 'My Movies'", conn);

                using (conn)
                {
                    try
                    {
                        int count = 0;
                        conn.Open();
                        count = (int)cmd.ExecuteScalar();
                    }
                    catch (SqlException ex)
                    {
                        ErrorMessage = ex.Message;
                        return false;
                    }
                }
                return true;
            }
        }

        public static string ErrorMessage { get; set; }

        public static void ResetContext()
        {
            MyMoviesContext = new MyMoviesDataContext(Settings.Default.MyMoviesConnectionString);
        }

        public List<Movie> GetMovies(IEnumerable<tblTitle> titles)
        {

            string[] ratings = new string[] { "", "Not Rated", "G", "PG", "PG-13", "NC-17", "R" };
            List<Movie> movies = new List<Movie>();
            foreach (tblTitle title in titles)
            {
                Movie movie = new Movie()
                {
                    Title = title.nvcOriginalTitle,
                    Description = title.ntxDescription,
                    SortTitle = title.nvcSortTitle,
                    Runtime = title.intRuntime.HasValue ? title.intRuntime.Value : 0,
                    MPAARatingDescription = title.nvcCertificationCause,
                    Rating = title.intRating,
                    ReleaseDate = title.datDVDRelease,
                    IMDBId = title.nvcIMDB,
                    Trailer = title.nvcTrailerUrl,
                    Watched = title.bitWatched,
                    DateAdded = title.datAdded
                };

                if (title.intCertification.HasValue && title.intCertification < ratings.Length && title.intCertification >= 0)
                    movie.MPAARating = ratings[title.intCertification.Value];

                if (title.intDirector.HasValue)
                {
                    tblPerson person = MyMoviesContext.tblPersons.SingleOrDefault(p => p.intId == title.intDirector.Value);
                    if (person != null)
                        movie.Director = person.nvcName;
                }

                var q = from tg in MyMoviesContext.tblTitleGenres
                        join g in MyMoviesContext.tblGenres on tg.intGenre equals g.intId
                        where tg.intTitle == title.intId
                        select new { GenreDesc = g.nvcLocalizedName };
                foreach (var n in q)
                    movie.Genres.Add(new Genre() { GenreDesc = n.GenreDesc });

                var q2 = from ts in MyMoviesContext.tblTitleStudios
                         join s in MyMoviesContext.tblStudios on ts.intStudio equals s.intId
                         where ts.intTitle == title.intId
                         orderby ts.intSort
                         select new { Name = s.nvcName };
                foreach (var n in q2)
                    movie.Studios.Add(new Studio() { Name = n.Name });

                var q3 = from tp in MyMoviesContext.tblTitlePersons
                         join s in MyMoviesContext.tblPersons on tp.intPerson equals s.intId
                         where tp.intTitle == title.intId
                         orderby tp.intSort
                         select new { Name = s.nvcName, Role = tp.nvcCharacter };
                foreach (var n in q3)
                    movie.Cast.Add(new CastMember() { Name = n.Name, Role = n.Role });



                List<tblDisc> discs = MyMoviesContext.tblDiscs.Where(d => d.intTitle == title.intId && d.nvcLocation != null && d.nvcLocation != "").ToList();
                if (discs.Count > 0)
                {
                    DirectoryInfo folder = new DirectoryInfo(discs.First().nvcLocation);
                    if (folder.Exists)
                    {
                        movie.BackgroundImageLocation = Path.Combine(folder.FullName, "background.jpg");
                        movie.PosterImageLocation = Path.Combine(folder.FullName, "folder.jpg");
                        //DirectoryInfo dvdFolder = folder.GetDirectories("VIDEO_TS").FirstOrDefault();
                        //if (dvdFolder != null)
                        //{
                        //    movie.Files.Add(dvdFolder.FullName);
                        //}

                        //foreach (FileInfo file in folder.GetFiles().OrderBy(f => f.Name))
                        //{
                        //    if (Path.GetFileNameWithoutExtension(file.Name) == "trailer" && extensions.Contains(file.Extension))
                        //    {
                        //        movie.Trailer = file.FullName;
                        //        continue;
                        //    }
                        //    else if (extensions.Contains(file.Extension))
                        //    {
                        //        movie.Files.Add(file.FullName);
                        //    }
                        //}

                    }
                }

                movie.OnGetFiles += new GetFilesEvent(movie_OnGetFiles);
                movie.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(movie_PropertyChanged);

                movie.Data = title;
                movies.Add(movie);
            }

            return movies;
        }

        void movie_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            Movie movie = sender as Movie;
            if (movie == null)
                return;
            tblTitle title = movie.Data as tblTitle;
            if (title == null)
                return;
            if (e.PropertyName == "Watched")
            {
                MyMoviesContext.ExecuteCommand("update tblTitles set bitWatched = @p0 where intId = @p1", movie.Watched, title.intId);
                //title.Watched = true;
            }
        }

        void movie_OnGetFiles(Movie movie)
        {
            tblTitle title = movie.Data as tblTitle;
            if (title == null)
                return;
            List<tblDisc> discs = MyMoviesContext.tblDiscs.Where(d => d.intTitle == title.intId && d.nvcLocation != null && d.nvcLocation != "").ToList();

            foreach (tblDisc disc in discs)
            {
                DirectoryInfo folder = new DirectoryInfo(disc.nvcLocation);
                if (folder.Exists)
                {
                    DirectoryInfo dvdFolder = folder.GetDirectories("VIDEO_TS").FirstOrDefault();
                    if (dvdFolder != null)
                    {
                        movie.Files.Add(dvdFolder.FullName);
                    }

                    foreach (FileInfo file in folder.GetFiles().OrderBy(f => f.Name))
                    {
                        if (Path.GetFileNameWithoutExtension(file.Name) == "trailer" && extensions.Contains(file.Extension))
                        {
                            movie.Trailer = file.FullName;
                            continue;
                        }
                        else if (extensions.Contains(file.Extension))
                        {
                            movie.Files.Add(file.FullName);
                        }
                    }

                }
            }
        }

        #region IMoviesBusiness Members

        public List<Movie> GetMovies()
        {
            return GetMovies(MyMoviesContext.tblTitles.OrderBy(t => t.nvcSortTitle));
        }

        public List<CommandItem> GetFilterItems()
        {
            List<CommandItem> filters = new List<CommandItem>();

            filters.Add(new CommandItem("Title", (int)FilterType.Title));
            filters.Add(new CommandItem("Last Added", (int)FilterType.LastAdded));
            filters.Add(new CommandItem("Not Watched", (int)FilterType.NotWatched));
            filters.Add(new CommandItem("Rating", (int)FilterType.Rating));
            filters.Add(new CommandItem("MPAA Rating", (int)FilterType.MPAARating));
            filters.Add(new CommandItem("Genre", (int)FilterType.Genre));
            filters.Add(new CommandItem("Release Date", (int)FilterType.ReleaseDate));
            filters.Add(new CommandItem("Runtime", (int)FilterType.Runtime));
            
            return filters;
        }

        public List<CommandItem> GetCommandItems()
        {
            List<CommandItem> commandItems = new List<CommandItem>();
            commandItems.Add(new CommandItem("All Titles", 0));
            commandItems.Add(new CommandItem("Categories", 1));
            commandItems.Add(new CommandItem("Genres", 2));
            commandItems.Add(new CommandItem("MPAA Ratings", 3));
            commandItems.Add(new CommandItem("Ratings", 4));
            commandItems.Add(new CommandItem("Series", 5));
            return commandItems;
        }

        public List<CommandItem> GetCommandItems(int id)
        {
            List<CommandItem> commandItems = new List<CommandItem>();
            MyMoviesDataContext ctx = MyMoviesContext;
            int count = 0;
            switch (id)
            {
                case 1:
                    Table<tblCategory> cats = ctx.GetTable<tblCategory>();
                    Table<tblTitleCategory> titlecats = ctx.GetTable<tblTitleCategory>();
                    var tc = from cat in cats
                             join titleCat in titlecats on cat.intId equals titleCat.intCategory
                             select new { Category = cat, TitleCategory = titleCat };
                    var cats2 = tc.Select(g1 => g1.Category).Distinct().OrderBy(g1 => g1.nvcName);
                    foreach (tblCategory c in cats2)
                    {
                        commandItems.Add(new CommandItem(string.Format("{0} ({1})", c.nvcName, tc.Select(q3 => q3.TitleCategory).Count(q2 => q2.intCategory == c.intId)), c.intId));
                    }
                    break;
                case 2:
                    Table<tblGenre> genres = ctx.GetTable<tblGenre>();
                    Table<tblTitleGenre> titleGenres = ctx.GetTable<tblTitleGenre>();
                    var qg = from genre in genres
                             join titleGenre in titleGenres on genre.intId equals titleGenre.intGenre
                             orderby genre.nvcName
                             select new { Genre = genre, TitleGenre = titleGenre };
                    var genres2 = qg.Select(g1 => g1.Genre).Distinct();
                    foreach (tblGenre c in genres2)
                    {
                        commandItems.Add(new CommandItem(string.Format("{0} ({1})", c.nvcName, qg.Select(q3 => q3.TitleGenre).Count(q2 => q2.intGenre == c.intId)), c.intId));
                    }
                    break;
                case 3:
                    var mpaaRatings = ctx.tblTitles.Select(t => t.intCertification);
                    count = mpaaRatings.Where(r => !r.HasValue).Count();
                    if (count > 0)
                        commandItems.Add(new CommandItem(string.Format("No Rating ({0})", count), null));

                    for (int x = 1; x < parentalRatings.Count; x++)
                    {
                        count = mpaaRatings.Where(r => r.Value == x).Count();
                        if (count > 0)
                            commandItems.Add(new CommandItem(string.Format("{0} ({1})", parentalRatings[x], count), x));
                    }
                    break;
                case 4:

                    var ratings = ctx.tblTitles.Select(t => t.intRating);
                    count = ratings.Where(r => !r.HasValue).Count();
                    if (count > 0)
                        commandItems.Add(new CommandItem(string.Format("No Rating ({0})", count), null));

                    foreach (int? rating in ratings.Distinct().OrderByDescending(r => r.Value))
                    {
                        count = ratings.Where(r => r.Value == rating).Count();
                        if (count > 0)
                            commandItems.Add(new CommandItem(string.Format("{0} ({1})", rating, count), rating));
                    }
                    break;
                case 5:
                    Table<tblTitleSery> seriesTable = ctx.GetTable<tblTitleSery>();
                    Table<tblTitle> titleTable = ctx.GetTable<tblTitle>();

                    var titleSeries = from series in seriesTable
                                      join title in titleTable on series.intTitle equals title.intId
                                      join titles in titleTable on series.intTitleSerie equals titles.intId
                                      orderby title.nvcSortTitle
                                      select new { Series = series, Title = title, Titles = titles };
                    var titleSeries2 = titleSeries.Select(g1 => g1.Title).Distinct();

                    foreach (var title in titleSeries2)
                    {
                        count = titleSeries.Where(r => r.Series.intTitle == title.intId).Count();
                        tblTitleSery ts = titleSeries.FirstOrDefault(r => r.Series.intTitle == title.intId).Series;
                        if (ts != null && !ts.bitExcludeHolder)
                            count++;
                        commandItems.Add(new CommandItem(string.Format("{0} ({1})", title.nvcOriginalTitle, count), title.intId));
                    }
                    break;
                default:
                    break;
            }


            return commandItems;
        }

        public List<Movie> GetMoviesFromCommand(int commandId, object data)
        {
            MyMoviesDataContext ctx = MyMoviesContext;         
            int? id = (int?)data;
            switch (commandId)
            {
                case 0:
                    return GetMovies(from m in ctx.tblTitles
                             orderby m.nvcSortTitle
                             select m);
                case 1:
                    tblCategory cat = ctx.tblCategories.SingleOrDefault(c => c.intId == id);
                    var intTitles = ctx.tblTitleCategories.Where(t => t.intCategory == cat.intId).Select(t => t.intTitle);
                    return GetMovies(from m in ctx.tblTitles
                                  where intTitles.Contains(m.intId)
                                  orderby m.nvcSortTitle
                                  select m);
                case 2:
                    tblGenre genre = ctx.tblGenres.SingleOrDefault(c => c.intId == id);
                    var intTitles2 = ctx.tblTitleGenres.Where(t => t.intGenre == genre.intId).Select(t => t.intTitle);
                    return GetMovies(from m in ctx.tblTitles
                                  where intTitles2.Contains(m.intId)
                                  orderby m.nvcSortTitle
                                  select m);
                case 3:
                    return GetMovies(from m in ctx.tblTitles
                                  where m.intCertification == id
                                  orderby m.nvcSortTitle
                                  select m);
                case 4:
                    return GetMovies(from m in ctx.tblTitles
                                  where m.intRating == id
                                  orderby m.nvcSortTitle
                                  select m);
                case 5:
                    var q = ctx.tblTitles.Where(t => t.intId == id);
                    if (q.Count() > 0)
                    {
                        var item = q.First();
                        var itemIds = ctx.tblTitleSeries.Where(t => t.intTitle == item.intId).Select(t => t.intTitleSerie).ToList();
                        itemIds.Add(item.intId);

                        return GetMovies(from m in ctx.tblTitles
                                      where itemIds.Contains(m.intId)
                                      orderby m.nvcSortTitle
                                      select m);
                    }
                    break;
                default:
                    break;
            }
            return new List<Movie>() ;
        }

        public bool SupportsWatched()
        {
            return true;
        }

        #endregion
    }
}
